//////////////////////////////////////////////////////////////////////////////////
					
///////////////////////////////////////////////////////////////////////////////////////
/*
 * OrderLineDAO.java
 *
 * Created on March 28, 2007, 7:32 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package edu.byu.isys413.samlam;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

/**
 * Order Line DAO
 * @author Prabol Bhandari
 */
public class OrderLineDAO {
    
    //singleton pattern
    private static OrderLineDAO instance = null;
    
    /** Creates a new instance of OrderLineDAO */
    public OrderLineDAO() {
    }
    
    /**
     * enforcement of the singleton pattern
     * @return the instance which will be used when the DAO is used
     */
    public static synchronized OrderLineDAO getInstance() {
        if (instance == null) {
            instance = new OrderLineDAO();
        }
        return instance;
    }
    
    
    //////////////////////////
    //CREATE
    /**
     * This method is used to create a business object of type Order line.
     * @return OrderLine
     * @throws edu.byu.isys413.estrogen.data.DataException if the GUID cannot be generated
     * @throws edu.byu.isys413.samlam.DataException exception
     */
    public OrderLine create() throws DataException {
        String id = null;
        try{
            id = GUID.generate();
        } catch (Exception e) {
            throw new DataException("Could not generate new Order Line", e);
        }
        OrderLine orderln = new OrderLine(id);
        Cache c = Cache.getInstance();
        c.put(orderln.getOlguid(), orderln);
        return orderln;
    }
    
    
    //////////////////////////
    //READ
    /**
     * This method reads the Order line object from the guid.
     * @param id the GUID for a particular Order line to be read
     * @return OrderLine with the matching id
     * @throws edu.byu.isys413.estrogen.data.DataException if there is an error with the SQL or the connection
     * @throws edu.byu.isys413.samlam.DataException exception
     * @throws java.lang.Exception exception
     */
    public synchronized OrderLine read(String id) throws DataException, Exception {
               
        OrderLine orderln = (OrderLine)Cache.getInstance().get(id);
        
        if (orderln == null) {
        Connection conn = null;
            try {
                conn = ConnectionPool.getInstance().get();

                orderln = read(id, conn);

                conn.commit();
                ConnectionPool.getInstance().release(conn);                
            }
            catch (DataException e) {
                throw new DataException("Could not get a connection to the database.");
            }
            catch (SQLException e) {
                //rollback
                try {
                    conn.rollback();
                    ConnectionPool.getInstance().release(conn);
                }
                catch (SQLException e2) {
                    throw new DataException("ERROR: Could not even release the connection", e2);
                }
                throw new DataException("Could not retrieve record for id=" + id, e);
            }
        }
        
        return orderln;
    }
    
    /**
     * This method reads the Order line object with the id and a connection
     * @param id the GUID for a particular Order line to be read
     * @param conn the connection to use for the reading
     * @return OrderLine with the specified id
     * @throws edu.byu.isys413.estrogen.data.DataException if there is any error in the reading
     * @throws java.sql.SQLException if there is any error in the SQL
     */
    public synchronized OrderLine read(String id, Connection conn) throws DataException, SQLException{  //will also create a BO, but it's pulling it off the database and creating the object to be used
        OrderLine orderln = (OrderLine)Cache.getInstance().get(id);
        
        if (orderln == null) {
                   
            try 
            {   
                PreparedStatement SELECT = conn.prepareStatement("SELECT * FROM orderline WHERE orderlineid = ?");
                SELECT.setString(1, id);
                ResultSet rs = SELECT.executeQuery();
                conn.commit();
                if (rs.next()) 
                {
                    orderln = new OrderLine(id);
                    String conID = rs.getString("conceptualID");
                    orderln.setConceptual((Conceptual)ConceptualDAO.getInstance().read(conID));
                    //orderln.setVendoritem(VendorItemDAO.getInstance().read(x));
                    orderln.setQuantity(rs.getInt("QUANTITY"));
                    orderln.setPurchaseOrder(PurchaseOrderDAO.getInstance().read(rs.getString("purchaseorderid"), conn));
                    Cache.getInstance().put(id, orderln);
                    orderln.setAlreadyInDB(true);
                }
                else
                {
                    throw new DataException("Object was not found in the database.");
                }

            }
            catch (SQLException e) 
            {
                    conn.rollback();
                    throw new DataException("Could not retrieve record for id=" + id, e);
            }
            catch (Exception e)
            {
                throw new DataException("error here...", e);
            }
        }
        
        return orderln;
    }
    
    /**
     * This method gets the connection and puts the Order line into the database.
     * @param tl the OrderLine to be saved
     * @throws edu.byu.isys413.estrogen.data.DataException if there are any errors while processing
     * @throws java.sql.SQLException if there are any errors executing the SQL
     */    
    public synchronized void save(OrderLine orderln) throws SQLException, DataException, Exception {
        Connection conn = null;
        try {
            conn = ConnectionPool.getInstance().get();
            System.out.println("Going to save with conn");
            save(orderln, conn);
            
            conn.commit();
            ConnectionPool.getInstance().release(conn);
        }
        catch (DataException e) {
            conn.rollback();
            throw new DataException("Could not connect to the database.", e);
        }
        
    }
    
    /**
     * This method puts the Order line into the database, either inserting
     * or updating depending on whether it exists in the database already.
     * @param tl the OrderLine to be saved
     * @param conn the connection to be used
     * @throws edu.byu.isys413.estrogen.data.DataException if there are any errors while processing
     * @throws java.sql.SQLException if there are any errors executing the SQL
     */    
    synchronized void save(OrderLine orderln, Connection conn) throws SQLException, DataException {
        if (orderln.isAlreadyInDB()){
            System.out.println("updating...");
            update(orderln, conn);    
        }   
        else {
            System.out.println("inserting...");
            insert(orderln, conn);
            
        }
        orderln.setDirty(false);
    }
    
     /**
     * This method saves the updated Order line to the database if it already exists.
     * @param orderln the OrderLine to be saved
     * @param conn the connection passed from the save method
     * @throws java.sql.SQLException if there are any problems executing the SQL
     * @throws edu.byu.isys413.estrogen.data.DataException if there are any other errors while executing the update
     */
    void update(OrderLine orderln, Connection conn) throws SQLException, DataException {
        try {
            System.out.println("OrderLnGUID:" + orderln.getOlguid());
            PreparedStatement UPDATE = conn.prepareStatement("UPDATE orderline SET purchaseorderid = ?, conceptualid = ?, quantity = ? WHERE orderlineid = ?");
            UPDATE.setString(1, orderln.getPurchaseOrder().getPoguid());
            UPDATE.setString(2, orderln.getConceptual().getId());
            UPDATE.setInt(3, orderln.getQuantity());
            UPDATE.setString(4, orderln.getOlguid());
            
            UPDATE.executeUpdate();            
            System.out.println("Update executed");
            UPDATE.close();
        }
        catch (SQLException e) {
            conn.rollback();
            throw new DataException("Could not perform the update on the Order Line", e);
        }
    }
    
    /**
     * This method saves the Order line into the database if it did not previously 
     * exist.
     * @param orderln the OrderLine to be saved
     * @param conn the connection passed from the save method
     * @throws java.sql.SQLException if there are any problems executing the SQL
     * @throws edu.byu.isys413.estrogen.data.DataException if there are any other errors while executing the insert
     */
    public void insert(OrderLine orderln, Connection conn) throws SQLException, DataException {
        try {
            PreparedStatement INSERT = conn.prepareStatement("INSERT INTO orderline VALUES (?, ?, ?, ?)" );
            INSERT.setString(1, orderln.getOlguid()); 
            INSERT.setString(3, orderln.getPurchaseOrder().getPoguid());
            INSERT.setString(2, orderln.getConceptual().getId());
            INSERT.setInt(4, orderln.getQuantity());
                  
            INSERT.executeUpdate();
            INSERT.close();
            
            orderln.setAlreadyInDB(true);
            
        }
        catch (SQLException e) {
            conn.rollback();
            throw new DataException("Could not insert the Order Line into the database", e);
        }
    }
    
    public List<OrderLine> getAllLinesForOrder(PurchaseOrder order, Connection conn) throws SQLException, DataException, Exception {
        List<OrderLine> orderlines = new LinkedList();
        
        try 
        {
            PreparedStatement getLines = conn.prepareStatement("SELECT * FROM orderline WHERE orderlineid = ?");
            getLines.setString(1, order.getPoguid());
            ResultSet rs = getLines.executeQuery();
            conn.commit();
            while (rs.next())
            {
                OrderLine orderln = new OrderLine(rs.getString("orderlineid"));
                orderln.setConceptual((Conceptual)ConceptualDAO.getInstance().read(rs.getString("conceptualID")));
                orderln.setPurchaseOrder(order);
                orderln.setQuantity(rs.getInt("quantity"));
                orderln.setAlreadyInDB(true);
                
                orderlines.add(orderln);
            } 
            
            return orderlines;
            
        }
        catch (SQLException e)
        {
            conn.rollback();
            throw new DataException("Could not read the lines from the database", e);
        }
    }
    
    /////////////////////////////////DELETE//////////////////////////////////////////////////
    //A delete method was intentionally omitted here for business reasons--we would like
    //to keep a record of all orderlines Because they are attached to orders and other essential elements
    //that should not be deleted from the database.  Rather than deleting them completely,
    //we might instead build in a "deleted" boolean variable where we can flag a customer if they are 
    //no longer active.
    
}